package com.landiao.mybatis.plugin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.Properties;
import java.util.Set;

import javax.xml.bind.PropertyException;

import org.apache.ibatis.executor.statement.BaseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;

import com.landiao.common.pojo.Page;

/**
 * Mybatis的分页查询插件，通过拦截StatementHandler的prepare方法来实现。
 * 只有在参数列表中包括Page类型的参数时才进行分页查询。 在多参数的情况下，只对第一个Page类型的参数生效。
 * 在参数列表中，Page类型的参数无需用@Param来标注
 */
@SuppressWarnings({"unchecked", "rawtypes"})
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PagePlugin implements Interceptor {

	private static String dialect = null;// 数据库类型

	public Object intercept(Invocation ivk) throws Throwable {
		if (ivk.getTarget() instanceof RoutingStatementHandler) {
			RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk
					.getTarget();
			BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper
					.getValueByFieldName(statementHandler, "delegate");
			MappedStatement mappedStatement = (MappedStatement) ReflectHelper
					.getValueByFieldName(delegate, "mappedStatement");
			/**
			 * 传入的参数是否有page参数，如果有，则分页，
			 */
			BoundSql boundSql = delegate.getBoundSql();
			Object parameterObject = boundSql.getParameterObject();// 分页SQL<select>中parameterType属性对应的实体参数，即Mapper接口中执行分页方法的参数,该参数不得为空
			if (parameterObject == null) {
				// throw new
				// NullPointerException("boundSql.getParameterObject() is null!");
				return ivk.proceed();
			} else {
				Page pageView = null;
				if (parameterObject instanceof Page) { // 参数就是Pages实体
					pageView = (Page) parameterObject;
				} else if (parameterObject instanceof Map) {
					for (Entry entry : (Set<Entry>) ((Map) parameterObject)
							.entrySet()) {
						if (entry.getValue() instanceof Page) {
							pageView = (Page) entry.getValue();
							break;
						}
					}
				} else { // 参数为某个实体，该实体拥有Pages属性
					pageView = ReflectHelper.getValueByFieldType(
							parameterObject, Page.class);
					if (pageView == null) {
						return ivk.proceed();
					}
				}
				if (pageView == null) {
					return ivk.proceed();
				}
				String sql = boundSql.getSql();
				Connection connection = (Connection) ivk.getArgs()[0];
				setPageParameter(sql, connection, mappedStatement, boundSql,
						parameterObject, pageView);
				String pageSql = generatePagesSql(sql, pageView);
				ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); // 将分页sql语句反射回BoundSql.
			}
			// }
		}
		return ivk.proceed();
	}

	/**
	 * 从数据库里查询总的记录数并计算总页数，回写进分页参数<code>PageParameter</code>,这样调用者就可用通过 分页参数
	 * <code>PageParameter</code>获得相关信息。
	 * 
	 * @param sql
	 * @param connection
	 * @param mappedStatement
	 * @param boundSql
	 * @param page
	 * @throws SQLException
	 */
	private void setPageParameter(String sql, Connection connection,
			MappedStatement mappedStatement, BoundSql boundSql,
			Object parameterObject, Page pageView) throws SQLException {
		// 记录总记录数
		PreparedStatement countStmt = null;
		ResultSet rs = null;
		try {
//			String countSql = "select count(1) from (" + sql + ") tmp_count"; // 记录统计
			String countSql = "SELECT COUNT(1) FROM "+suffixStr(sql); // 记录统计
			countStmt = connection.prepareStatement(countSql);
			ReflectHelper.setValueByFieldName(boundSql, "sql", countSql);
			DefaultParameterHandler parameterHandler = new DefaultParameterHandler(
					mappedStatement, parameterObject, boundSql);
			parameterHandler.setParameters(countStmt);
			rs = countStmt.executeQuery();
			int count = 0;
			if (rs.next()) {
				count = ((Number) rs.getObject(1)).intValue();
			}
			pageView.setRecords(count);
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
			}
			try {
				countStmt.close();
			} catch (Exception e) {
			}
		}

	}

	/**
	   *   select
	     *  id,
		 * 	articleNo,
		 * 	articleName,
	     *  (SELECT loginName from userinfo u where u.id=userId) loginName,
		 * 	(SELECT userName from userinfo u where u.id=userId) userName,
		 * from article	
		 * 兼容以上子查询
		 * //去除sql ..from 前面的字符
	   */
	public static String suffixStr(String toSql){
		Pattern p=Pattern.compile("FROM");
		Matcher matcher=p.matcher(toSql.toUpperCase());
		int pos=0;
		while(matcher.find()){//循环from关键字所出现的位置
			int st= matcher.start();
			String zb = "";
			if(pos==0){
				zb = toSql.substring(0,st+4);
				pos=st;
			}else{
				zb = toSql.substring(pos,st+4);
				pos=st;
			}
			if(zb.indexOf("(")==-1){//from之前是否有括号
				String pre = toSql.substring(st-1,st);
				String suf = toSql.substring(st+4,st+5);
				if(pre.trim().isEmpty()&&suf.trim().isEmpty()){//判断from的前后是否为空
					return toSql.substring(st+4);
				}
			}
		}
		return toSql;
	}
	
	/**
	 * 根据数据库方言，生成特定的分页sql
	 * 
	 * @param sql
	 * @param page
	 * @return
	 */
	private String generatePagesSql(String sql, Page page) {
		if (page != null) {
			if ("mysql".equals(dialect)) {
				return buildPageSqlForMysql(sql, page).toString();
			} else if ("oracle".equals(dialect)) {
				return buildPageSqlForOracle(sql, page).toString();
			}
		}
		return sql;
	}

	/**
	 * mysql的分页语句
	 * 
	 * @param sql
	 * @param page
	 * @return String
	 */
	public StringBuilder buildPageSqlForMysql(String sql, Page page) {
		StringBuilder pageSql = new StringBuilder(100);
		String beginrow = String.valueOf((page.getPage() - 1)
				* page.getPageSize());
		pageSql.append(sql);
		pageSql.append(" limit " + beginrow + "," + page.getPageSize());
		return pageSql;
	}

	/**
	 * 参考hibernate的实现完成oracle的分页
	 * 
	 * @param sql
	 * @param page
	 * @return String
	 */
	public StringBuilder buildPageSqlForOracle(String sql, Page page) {
		StringBuilder pageSql = new StringBuilder(100);
		String beginrow = String.valueOf((page.getPage() - 1)
				* page.getPageSize());
		String endrow = String.valueOf(page.getPage() * page.getPageSize());

		pageSql.append("select * from ( select temp.*, rownum row_id from ( ");
		pageSql.append(sql);
		pageSql.append(" ) temp where rownum <= ").append(endrow);
		pageSql.append(") where row_id > ").append(beginrow);
		return pageSql;
	}

	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	public void setProperties(Properties p) {
		dialect = p.getProperty("dialect");
		if (isEmpty(dialect)) {
			try {
				throw new PropertyException(
						"dialectName or dialect property is not found!");
			} catch (PropertyException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 判断变量是否为空
	 * 
	 * @param s
	 * @return
	 */
	public boolean isEmpty(String s) {
		if (null == s || "".equals(s) || "".equals(s.trim())
				|| "null".equalsIgnoreCase(s)) {
			return true;
		} else {
			return false;
		}
	}
	
	public static void main(String[] args) { 
		String sql = "select fromid from goods_batch_stock";
		String countSql = "SELECT COUNT(1) FROM "+suffixStr(sql); // 记录统计
		System.out.println(countSql);
	}
}
